package com.trabalho1.db.dao;

import com.trabalho1.db.ConnectionFactory;
import com.trabalho1.model.ModelTelefone;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DaoTelefone 
{    
    private Connection con;
    private PreparedStatement stmt;
    
    private void executeSQL(String sql) 
    {
        try 
        {
            con  = ConnectionFactory.getConnection();
            stmt = con.prepareStatement(sql);
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoTelefone.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private void closeConnection() 
    {
        try 
        {
            stmt.close();
            con.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoTelefone.class.getName()).log(Level.SEVERE, null, ex);
        }
    }    

    public void insert(ModelTelefone mdlTelefone)
    {
        this.executeSQL("INSERT INTO telefone (id_usuario, ddd, telefone) VALUES (?, ?, ?)");
       
        try 
        {
            Integer i = 0;
            
            stmt.setInt(++i, mdlTelefone.getId_usuario());
            stmt.setInt(++i, mdlTelefone.getDdd());
            stmt.setInt(++i, mdlTelefone.getTelefone());
            stmt.execute();   
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoTelefone.class.getName()).log(Level.SEVERE, null, ex);
        } 
        finally 
        {
            this.closeConnection();
        }
    }
    
    public void update(ModelTelefone mdlTelefone) 
    {
        this.executeSQL("UPDATE telefone SET id_usuario = ?, ddd = ?, telefone = ? WHERE id_telefone = ?");
        
        try 
        {            
            Integer i = 0;
            
            stmt.setInt(++i, mdlTelefone.getId_usuario());
            stmt.setInt(++i, mdlTelefone.getDdd());
            stmt.setInt(++i, mdlTelefone.getTelefone());
            stmt.setInt(++i, mdlTelefone.getId_telefone());
            stmt.execute();            
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoTelefone.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
    }

    public void delete(ModelTelefone mdlTelefone)
    {
        this.executeSQL("DELETE FROM telefone WHERE id_telefone = ?");
        
        try 
        {
            stmt.setInt(1, mdlTelefone.getId_telefone());
            stmt.execute();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoTelefone.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
    }    
    
    public List<ModelTelefone> findAll(Integer id_usuario) 
    {
        List<ModelTelefone> telefones = new ArrayList<ModelTelefone>();
        
        String sql = "SELECT * FROM telefone ";
                
        if (id_usuario > 0)
        {
            sql+= "WHERE id_usuario = ?";
        }
        
        sql+= "ORDER BY id_usuario, ddd, telefone";
        
        this.executeSQL(sql);
        
        ResultSet rset;
        
        try 
        {
            if (id_usuario > 0)
            {
                stmt.setInt(1, id_usuario);
            }
            
            rset = stmt.executeQuery();
            
            while (rset.next()) 
            {
                ModelTelefone mdlTelefone = new ModelTelefone();
                mdlTelefone.setId_telefone(rset.getInt("id_telefone"));
                mdlTelefone.setId_usuario(rset.getInt("id_usuario"));
                mdlTelefone.setDdd(rset.getInt("ddd"));
                mdlTelefone.setTelefone(rset.getInt("telefone"));
                telefones.add(mdlTelefone);
            }
            
            rset.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
       
        return telefones;
    }   
    
    public ModelTelefone findOne(ModelTelefone mdlTelefone)
    {
        Integer id_telefone = mdlTelefone.getId_telefone();
        
        this.executeSQL("SELECT * FROM telefone WHERE id_telefone = ? LIMIT 1");
        ResultSet rset;
        
        try 
        {
            stmt.setInt(1, id_telefone);
            rset = stmt.executeQuery();
            rset.next();
            
            mdlTelefone = new ModelTelefone();
            mdlTelefone.setId_telefone(rset.getInt("id_telefone"));
            mdlTelefone.setId_usuario(rset.getInt("id_usuario"));
            mdlTelefone.setDdd(rset.getInt("ddd"));
            mdlTelefone.setTelefone(rset.getInt("telefone"));
         
            rset.close();
        } 
        catch (SQLException ex) 
        {
            Logger.getLogger(DaoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            this.closeConnection();
        }
        
        return mdlTelefone;
    }
}